package com.bjkcst.baselib.excel;

import android.content.Context;
import android.os.Environment;
import android.util.Log;


import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * @author: zgx
 * @email: zhougaoxing@bjkcst.com
 * @date: 2022/11/18
 * @describe:
 */

public class ExcelUtil {


    private static final String TAG = "ExcelUtil";


    public static final String EXCEL_DIR = "/KingKong/app/excel";
    public static final String SCHEDULE_TABLE_SUFFIX = "schedule.xls";
    public static final String EXCEL_TABLE_NAME = "operate";
    public static final int DEFAULT_SHEET_INDEX = 0;

    private static WritableFont arial14font = null;
    private static WritableCellFormat arial14format = null;
    private static WritableFont arial10font = null;
    private static WritableCellFormat arial10format = null;
    private static WritableFont arial12font = null;
    private static WritableCellFormat arial12format = null;
    private final static String UTF8_ENCODING = "UTF-8";

    private final static int COL_NAME_START_INDEX = 2;


    /**
     * 单元格的格式设置 字体大小 颜色 对齐方式、背景颜色等...
     */
    private static void format() {
        try {
            arial14font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
            arial14font.setColour(jxl.format.Colour.LIGHT_BLUE);
            arial14format = new WritableCellFormat(arial14font);
            arial14format.setAlignment(jxl.format.Alignment.CENTRE);
            arial14format.setVerticalAlignment(VerticalAlignment.CENTRE);//上下居中
            arial14format.setWrap(true);
            arial14format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            arial14format.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);

            arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
            arial10format = new WritableCellFormat(arial10font);
            arial10format.setAlignment(jxl.format.Alignment.CENTRE);
            arial10format.setVerticalAlignment(VerticalAlignment.CENTRE);
            arial10format.setWrap(true);
            arial10format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            arial10format.setBackground(Colour.GRAY_25);

            arial12font = new WritableFont(WritableFont.ARIAL, 12);
            arial12format = new WritableCellFormat(arial12font);
            arial12format.setAlignment(jxl.format.Alignment.CENTRE);
            arial12format.setVerticalAlignment(VerticalAlignment.CENTRE);
            arial12format.setWrap(true);
            arial12format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);


        } catch (WriteException e) {
            e.printStackTrace();
        }
    }


    /**
     * 初始化Excel表格
     *
     * @param filePath  filePath
     * @param sheetName Excel表格的表名
     * @param colName   excel中包含的列名（可以有多个）
     */
    public static void initExcel(String filePath, String sheetName, String[] colName) {
        format();
        WritableWorkbook workbook = null;

        try {
            File file = new File(Environment.getExternalStorageDirectory().getPath() + EXCEL_DIR);
            makeDir(file);
            File saveFile = new File(file, filePath);
            if (!saveFile.exists()) {
                saveFile.createNewFile();
            }

            workbook = Workbook.createWorkbook(saveFile);
            //设置表格的名字
            WritableSheet sheet = workbook.createSheet(sheetName, DEFAULT_SHEET_INDEX);
            //创建标题栏
//            sheet.addCell((WritableCell) new Label(0, 0, "系统", arial10format));
//
//
//            int eastEndPosition = EAST_SLOT_GROUP_NUM * EACH_SLOT_GROUP_NUM;
//            int westEndPosition = (EAST_SLOT_GROUP_NUM + WEST_SLOT_GROUP_NUM) * EACH_SLOT_GROUP_NUM;
//
//            sheet.mergeCells(0, 1, 0, eastEndPosition);
//            sheet.addCell((WritableCell) new Label(0, 1, "东系统", arial10format));
//
//            sheet.mergeCells(0, eastEndPosition + 1, 0, westEndPosition);
//            sheet.addCell((WritableCell) new Label(0, eastEndPosition + 1, "西系统", arial10format));
//
//            sheet.addCell((WritableCell) new Label(1, 0, "槽组/日期", arial10format));
//
//            for (int col = 1; col < EXCEL_SLOT_GROUP.size() + 1; col++) {
//                sheet.addCell(new Label(1, col, EXCEL_SLOT_GROUP.get(col - 1), arial10format));
//            }
//
//            for (int col = COL_NAME_START_INDEX; col < colName.length + COL_NAME_START_INDEX; col++) {
//                sheet.addCell(new Label(col, 0, colName[col - COL_NAME_START_INDEX], arial10format));
//            }
            //设置行高
            sheet.setRowView(0, 1000);
            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 将制定类型的List写入Excel中
     *
     * @param objList  待写入的list
     * @param fileName fileName
     * @param c        Context
     */
    @SuppressWarnings("unchecked")
    public static void writeObjListToExcel(List<List<String>> objList, String fileName, Context c) {
        if (objList != null && objList.size() > 0) {
            WritableWorkbook writebook = null;
            InputStream in = null;
            try {
                WorkbookSettings setEncode = new WorkbookSettings();
                setEncode.setEncoding(UTF8_ENCODING);
                File file = new File(Environment.getExternalStorageDirectory().getPath() + EXCEL_DIR);
                makeDir(file);
                File saveFile = new File(file, fileName);
                if (!saveFile.exists()) {
                    saveFile.createNewFile();
                }

                in = new FileInputStream(saveFile);
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(saveFile, workbook);
                WritableSheet sheet = writebook.getSheet(DEFAULT_SHEET_INDEX);


                for (int j = 0; j < objList.size(); j++) {
                    ArrayList<String> list = (ArrayList<String>) objList.get(j);
                    for (int i = 0; i < list.size(); i++) {
                        sheet.addCell(new Label(i + 2, j + 1, list.get(i), arial12format));
                        sheet.setColumnView(i + 2, list.get(i).length() + 8);
                    }
                    //设置行高
                    sheet.setRowView(j + 1, 350);
                }

                writebook.write();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (writebook != null) {
                    try {
                        writebook.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                }
                if (in != null) {
                    try {
                        in.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }

        }
    }


    /**
     *
     * @param fileName 文件名称
     * @param sheetIndex 表index 0
     * @param rowStartIndex
     * @param rowEndIndex
     * @param columnsStartIndex
     * @param columnsEndIndex
     * @return
     */
    public static List<ExcelDataBean> readExcel(String fileName, int sheetIndex, int rowStartIndex, int rowEndIndex, int columnsStartIndex, int columnsEndIndex) {
        ArrayList<ExcelDataBean> list = new ArrayList<>();
        File file = new File(Environment.getExternalStorageDirectory().getPath() + EXCEL_DIR);
        File saveFile = new File(file, fileName);
        if (!saveFile.exists()) {
            Log.i(TAG, "readExcel excel is not exist");
            return list;
        }

        //创建输入流
        InputStream stream = null;
        try {
            stream = new FileInputStream(saveFile);
            Workbook rwb = Workbook.getWorkbook(stream);
            Sheet sheet = rwb.getSheet(sheetIndex);
            if (rowStartIndex >= rowEndIndex || columnsStartIndex >= columnsEndIndex) {
                Log.i(TAG, "rowStartIndex or columnsStartIndex error ");
                return list;
            }
            //行数(表头的目录不需要，从1开始)
            for (int i = rowStartIndex; i < rowEndIndex; i++) {
                Cell cell = null;
                //列数
                for (int j = columnsStartIndex; j < columnsEndIndex; j++) {
                    //获取第i行，第j列的值
                    cell = sheet.getCell(j, i);
                    list.add(new ExcelDataBean(i, j, cell.getContents()));
                }
            }
        } catch (BiffException | IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static void makeDir(File dir) {
        if (!dir.getParentFile().exists()) {
            makeDir(dir.getParentFile());
        }
        dir.mkdir();
    }
}
